package com.projet.zys;

import cn.hutool.core.util.IdUtil;
import com.constant.DbConfig;
import com.core.common.FnKit;
import com.core.dao.builder.InsertBuilder;
import com.core.excel.ExcelKit;
import com.module.material.entity.MaterialVO;
import com.module.material.service.MaterialService;

import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 读取Excel，生产其他出入库数据，同时生产出入库记录，但是不产生库存
 */
public class ReadExcelZys {
    public static void main(String[] args) {
        String dir = "C:\\Users\\Administrator\\Desktop\\zys\\";
        String batchId = IdUtil.getSnowflake(1, 2).nextId() + "";
        try {
            MaterialVO matCond = new MaterialVO();
            matCond.setTenantId("725731");
            List<MaterialVO> materialVOList = MaterialService.query(matCond, DbConfig.getZysSystem());
            Map<String, MaterialVO> materialMap = FnKit.map(materialVOList, m -> m.getMaterialNo());
            MaterialVO mat;


            Map<Integer, List<Map<String, String>>> res = ExcelKit.analysis(dir + "data.xlsx", 0);
            List<Map<String, String>> dataList = res.get(0);
            List<String> headList = new ArrayList<>();
            Map<String, Integer> seqMap = new HashMap<>();
            Integer idx;
            for (Map<String, String> b : dataList) {
                String materialNo = nvl(b.get("物料编码"));
                mat = materialMap.get(materialNo);
                if (mat == null) {
                    System.out.println(materialNo + "物料不存在");
                    return;
                }

                String quantity = nvl(b.get("入库数量"));
                String prodDate = b.get("供货批次");
                String prodBatch = prodDate.replace("-", "");
                idx = seqMap.get(prodBatch);
                if (idx == null) {
                    idx = 0;
                }
                idx++;
                seqMap.put(prodBatch, idx);
                String codeSeq = String.valueOf(10000 + idx).substring(1);
                Long id = IdUtil.getSnowflake(1, 2).nextId();
                InsertBuilder s4 = new InsertBuilder("lgs_other_io_warehouse");
                s4.append("id", id);
                s4.append("io_code", prodBatch + codeSeq);
                s4.append("io_type", "entry");
                s4.appendNumber("io_num", quantity);
                s4.appendNumber("package_std_num", quantity);
                s4.appendNumber("warehouse_num", quantity);
                s4.appendNumber("material_id", String.valueOf(mat.getId()));
                s4.append("material_no", mat.getMaterialNo());
                s4.append("material_type", mat.getMaterialType());
                s4.append("package_no", mat.getUnit());
                s4.append("business_type", "in");
                s4.append("operator_user_name", "严焕");

                s4.appendNumber("production_batch", prodBatch);
                s4.appendNumber("warehouse_id", "1933727544038760450");
                s4.appendNumber("area_id", "1933730357439471617");
                s4.appendNumber("loc_id", "1933730458979377154");
                s4.appendDatetime("create_time", prodDate + " 08:30:00");
                s4.appendDatetime("update_time", prodDate + " 08:30:00");
                s4.appendNumber("is_deleted", "0");
                s4.appendNumber("status", "0");
                s4.appendNumber("create_user", "10203");
                s4.appendNumber("update_user", batchId);
                s4.append("reason", "调整库存");
                s4.append("tenant_id", "725731");
                headList.add(s4.toString());

                InsertBuilder s3 = new InsertBuilder("lgs_other_io_warehouse_packaging");
                Long subId = IdUtil.getSnowflake(1, 2).nextId();
                s3.append("id", subId);
                s3.append("io_id", id);
                s3.append("flow_no", materialNo + "------" + subId);
                s3.appendNumber("package_num", quantity);
                s3.appendNumber("package_std_num", quantity);
                s3.append("package_unit", mat.getUnit());
                s3.appendDatetime("create_time", prodDate + " 08:30:00");
                s3.appendDatetime("update_time", prodDate + " 08:30:00");
                s3.appendNumber("is_deleted", "0");
                s3.appendNumber("status", "0");
                s3.appendNumber("create_user", "10203");
                s3.appendNumber("update_user", batchId);
                s3.append("tenant_id", "725731");
                headList.add(s3.toString());


                id = IdUtil.getSnowflake(1, 2).nextId();
                InsertBuilder s2 = new InsertBuilder("lgs_other_io_warehouse");
                s2.append("id", id);
                s2.append("io_code", prodBatch + codeSeq);
                s2.append("io_type", "out");
                s2.appendNumber("io_num", quantity);
                s2.appendNumber("package_std_num", quantity);
                s2.appendNumber("warehouse_num", quantity);
                s2.appendNumber("material_id", String.valueOf(mat.getId()));
                s2.append("material_no", mat.getMaterialNo());
                s2.append("material_type", mat.getMaterialType());
                s2.append("package_no", mat.getUnit());
                s2.append("business_type", "out");
                s2.append("operator_user_name", "严焕");
                s2.appendNumber("production_batch", prodBatch);
                s2.appendNumber("warehouse_id", "1933727544038760450");
                s2.appendNumber("area_id", "1933730357439471617");
                s2.appendNumber("loc_id", "1933730458979377154");
                s2.appendDatetime("create_time", prodDate + " 13:30:00");
                s2.appendDatetime("update_time", prodDate + " 13:30:00");
                s2.appendNumber("is_deleted", "0");
                s2.appendNumber("status", "0");
                s2.appendNumber("create_user", "10203");
                s2.appendNumber("update_user", batchId);
                s2.append("reason", "调整库存");
                s2.append("tenant_id", "725731");
                headList.add(s2.toString());


                InsertBuilder s1 = new InsertBuilder("lgs_other_io_warehouse_packaging");
                subId = IdUtil.getSnowflake(1, 2).nextId();
                s1.append("id", subId);
                s1.append("io_id", id);
                s1.append("flow_no", materialNo + "------" + subId);
                s1.appendNumber("package_num", quantity);
                s1.appendNumber("package_std_num", quantity);
                s1.append("package_unit", mat.getUnit());
                s1.appendDatetime("create_time", prodDate + " 13:30:00");
                s1.appendDatetime("update_time", prodDate + " 13:30:00");
                s1.appendNumber("is_deleted", "0");
                s1.appendNumber("status", "0");
                s1.appendNumber("create_user", "10203");
                s1.appendNumber("update_user", batchId);
                s1.append("tenant_id", "725731");
                headList.add(s1.toString());
            }
            headList.add("");
            headList.add("");
            headList.add("UPDATE lgs_other_io_warehouse set create_time =  TIMESTAMPADD(SECOND, round(rand() * (3600 - 10) + 10), create_time) where  update_user =  " + batchId);
            headList.add("UPDATE lgs_other_io_warehouse set update_time =  TIMESTAMPADD(SECOND, round(rand() * (3600 - 10) + 10), create_time) where  update_user =  " + batchId);
            headList.add("UPDATE lgs_other_io_warehouse set operation_time =  create_time  where  update_user =  " + batchId);
            headList.add("");
            headList.add("");
            //入库单
            String sql;
            sql = " INSERT INTO lgs_entry_warehouse ( id, create_user, create_time, update_user, update_time, status, is_deleted, tenant_id, create_user_name, update_user_name, doc_no, type, operator_user_name, operation_time )";
            sql += " select  id, create_user, create_time, update_user, update_time, status, is_deleted, tenant_id, '严焕' create_user_name, '严焕' update_user_name, concat('RK',io_code) doc_no, '其它入库' type, operator_user_name, operation_time from  lgs_other_io_warehouse";
            sql += " where  io_type = 'entry' and update_user =  " + batchId;
            headList.add(sql);

            sql = " INSERT INTO lgs_entry_warehouse_item_batch ( id, create_user, create_time, update_user, update_time, status, is_deleted, tenant_id,  entry_id, material_id, material_no, material_type, packaging_unit_capacity_final, packaging_unit_name, production_batch, quantity, packaging_quantity, warehouse_type, warehouse_code )";
            sql += " select  id, create_user, create_time, update_user, update_time, status, is_deleted, tenant_id,  id entry_id, material_id, material_no, material_type, io_num packaging_unit_capacity_final, 'kg' packaging_unit_name, production_batch, io_num quantity, 1 packaging_quantity, 'lineSide' warehouse_type, '05' warehouse_code from  lgs_other_io_warehouse";
            sql += " where  io_type = 'entry' and update_user =  " + batchId;
            headList.add(sql);

            sql = " INSERT INTO lgs_entry_warehouse_packaging ( id, create_user, create_time, update_user, update_time, status, is_deleted, tenant_id,  packaging_label, packaging_unit_name, material_id, production_batch, production_date, warehouse_id, area_id, loc_id, entry_id, entry_item_batch_id, production_batch2, is_primary, is_confirm, quantity )";
            sql += " select  t.id, t.create_user, t.create_time, t.update_user, t.update_time, t.status, t.is_deleted, t.tenant_id,  p.flow_no packaging_label, 'kg' packaging_unit_name, t.material_id, t.production_batch, t.create_time production_date, t.warehouse_id, t.area_id, t.loc_id, t.id entry_id, t.id entry_item_batch_id, t.production_batch production_batch2, 0 is_primary, 0 is_confirm, io_num quantity from  lgs_other_io_warehouse t , lgs_other_io_warehouse_packaging p";
            sql += " where t.id = p.io_id and  t.io_type = 'entry'  and t.update_user =  " + batchId;
            headList.add(sql);
            headList.add("");
            headList.add("");
            sql = "  UPDATE lgs_entry_warehouse_item_batch ";
            sql += "   JOIN qp_imsp_system.md_material ON qp_imsp_system.md_material.material_no = lgs_entry_warehouse_item_batch.material_no and qp_imsp_system.md_material.tenant_id = lgs_entry_warehouse_item_batch.tenant_id";
            sql += "    SET lgs_entry_warehouse_item_batch.material_id = qp_imsp_system.md_material.id, ";
            sql += "        lgs_entry_warehouse_item_batch.material_type = qp_imsp_system.md_material.material_type,";
            sql += "        lgs_entry_warehouse_item_batch.material_name = qp_imsp_system.md_material.material_name,";
            sql += "        lgs_entry_warehouse_item_batch.unit_name = qp_imsp_system.md_material.unit,";
            sql += "        lgs_entry_warehouse_item_batch.unit_code = qp_imsp_system.md_material.unit,";
            sql += "        lgs_entry_warehouse_item_batch.specifications = qp_imsp_system.md_material.specifications,";
            sql += "        lgs_entry_warehouse_item_batch.model = qp_imsp_system.md_material.model";
            sql += "  WHERE lgs_entry_warehouse_item_batch.update_user =  " + batchId;
            headList.add(sql);
            headList.add("");
            headList.add("");
            sql = "   UPDATE lgs_entry_warehouse_packaging JOIN lgs_warehouse   ON lgs_warehouse.id = lgs_entry_warehouse_packaging.warehouse_id ";
            sql += "     SET lgs_entry_warehouse_packaging.warehouse_code = lgs_warehouse.warehouse_code, ";
            sql += "         lgs_entry_warehouse_packaging.warehouse_name = lgs_warehouse.warehouse_name";
            sql += "   WHERE lgs_entry_warehouse_packaging.update_user =  " + batchId;
            headList.add(sql);

            sql = "   UPDATE lgs_entry_warehouse_packaging JOIN lgs_warehouse_area   ON lgs_warehouse_area.id = lgs_entry_warehouse_packaging.area_id ";
            sql += "     SET lgs_entry_warehouse_packaging.area_code = lgs_warehouse_area.area_code,";
            sql += "         lgs_entry_warehouse_packaging.area_name = lgs_warehouse_area.area_name";
            sql += "   WHERE lgs_entry_warehouse_packaging.update_user =  " + batchId;
            headList.add(sql);

            sql = "   UPDATE lgs_entry_warehouse_packaging JOIN lgs_warehouse_loc   ON lgs_warehouse_loc.id = lgs_entry_warehouse_packaging.loc_id ";
            sql += "     SET lgs_entry_warehouse_packaging.loc_code = lgs_warehouse_loc.loc_code,";
            sql += "         lgs_entry_warehouse_packaging.loc_name = lgs_warehouse_loc.loc_name";
            sql += "   WHERE lgs_entry_warehouse_packaging.update_user =  " + batchId;
            headList.add(sql);

            //出库单
            headList.add("");
            headList.add("");
            sql = " INSERT INTO lgs_out_warehouse ( id, business_type, create_user, create_time, update_user, update_time, status, is_deleted, tenant_id, create_user_name, update_user_name, doc_no, type, operator_user_name, operation_time )";
            sql += " select  id, 'out' as business_type,create_user, create_time, update_user, update_time, status, is_deleted, tenant_id, '严焕' create_user_name, '严焕' update_user_name, concat('RK',io_code) doc_no, '其它出库' type, operator_user_name, operation_time from  lgs_other_io_warehouse";
            sql += " where  io_type = 'out' and update_user =  " + batchId;
            headList.add(sql);

            sql = " INSERT INTO lgs_out_warehouse_item_batch ( id, create_user, create_time, update_user, update_time, status, is_deleted, tenant_id,  out_id, material_id, material_no, material_type, packaging_unit_capacity_final, packaging_unit_name, production_batch, quantity, packaging_quantity, warehouse_type, warehouse_code )";
            sql += " select  id, create_user, create_time, update_user, update_time, status, is_deleted, tenant_id,  id out_id, material_id, material_no, material_type, io_num packaging_unit_capacity_final, 'kg' packaging_unit_name, production_batch, io_num quantity, 1 packaging_quantity, 'lineSide' warehouse_type, '05' warehouse_code from  lgs_other_io_warehouse";
            sql += " where  io_type = 'out' and update_user =  " + batchId;
            headList.add(sql);


            sql = " INSERT INTO lgs_out_warehouse_packaging ( id, create_user, create_time, update_user, update_time, status, is_deleted, tenant_id,  packaging_label, packaging_unit_name, material_id, production_batch, production_date, warehouse_id, area_id, loc_id, out_id, out_item_batch_id, production_batch2, quantity )";
            sql += " select  t.id, t.create_user, t.create_time, t.update_user, t.update_time, t.status, t.is_deleted, t.tenant_id,  p.flow_no packaging_label, 'kg' packaging_unit_name, t.material_id, t.production_batch, t.create_time production_date, t.warehouse_id, t.area_id, t.loc_id, t.id out_id, t.id out_item_batch_id, t.production_batch production_batch2, io_num quantity from  lgs_other_io_warehouse t , lgs_other_io_warehouse_packaging p";
            sql += "  where t.id = p.io_id and  t.io_type = 'out'   and t.update_user =  " + batchId;
            headList.add(sql);
            headList.add("");
            headList.add("");
            sql = "  UPDATE lgs_out_warehouse_item_batch ";
            sql += "  JOIN qp_imsp_system.md_material ON qp_imsp_system.md_material.material_no = lgs_out_warehouse_item_batch.material_no and qp_imsp_system.md_material.tenant_id = lgs_out_warehouse_item_batch.tenant_id";
            sql += "   SET lgs_out_warehouse_item_batch.material_id = qp_imsp_system.md_material.id, ";
            sql += "       lgs_out_warehouse_item_batch.material_type = qp_imsp_system.md_material.material_type,";
            sql += "       lgs_out_warehouse_item_batch.material_name = qp_imsp_system.md_material.material_name,";
            sql += "       lgs_out_warehouse_item_batch.unit_name = qp_imsp_system.md_material.unit,";
            sql += "       lgs_out_warehouse_item_batch.unit_code = qp_imsp_system.md_material.unit,";
            sql += "       lgs_out_warehouse_item_batch.specifications = qp_imsp_system.md_material.specifications,";
            sql += "       lgs_out_warehouse_item_batch.model = qp_imsp_system.md_material.model";
            sql += " WHERE lgs_out_warehouse_item_batch.update_user =  " + batchId;
            headList.add(sql);
            headList.add("");
            headList.add("");

            sql = "   UPDATE lgs_out_warehouse_packaging JOIN lgs_warehouse   ON lgs_warehouse.id = lgs_out_warehouse_packaging.warehouse_id ";
            sql += "     SET lgs_out_warehouse_packaging.warehouse_code = lgs_warehouse.warehouse_code, ";
            sql += "         lgs_out_warehouse_packaging.warehouse_name = lgs_warehouse.warehouse_name";
            sql += "   WHERE lgs_out_warehouse_packaging.update_user =  " + batchId;
            headList.add(sql);

            sql = "   UPDATE lgs_out_warehouse_packaging JOIN lgs_warehouse_area   ON lgs_warehouse_area.id = lgs_out_warehouse_packaging.area_id ";
            sql += "     SET lgs_out_warehouse_packaging.area_code = lgs_warehouse_area.area_code,";
            sql += "         lgs_out_warehouse_packaging.area_name = lgs_warehouse_area.area_name";
            sql += "   WHERE lgs_out_warehouse_packaging.update_user =  " + batchId;
            headList.add(sql);

            sql = "   UPDATE lgs_out_warehouse_packaging JOIN lgs_warehouse_loc   ON lgs_warehouse_loc.id = lgs_out_warehouse_packaging.loc_id ";
            sql += "     SET lgs_out_warehouse_packaging.loc_code = lgs_warehouse_loc.loc_code,";
            sql += "         lgs_out_warehouse_packaging.loc_name = lgs_warehouse_loc.loc_name";
            sql += "   WHERE lgs_out_warehouse_packaging.update_user =  " + batchId;
            headList.add(sql);


            StringBuilder s2 = new StringBuilder();
            for (String s : headList) {
                if (s.length() > 1) {
                    s2.append(s.trim() + ";\n");
                } else {
                    s2.append("\n");
                }

            }
            Files.write(Paths.get(dir + "data" + System.currentTimeMillis() + ".txt"), s2.toString().getBytes());
            System.out.println("\n\n文件生产完成，数据行：" + headList.size());
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }


    private static String nvl(String cellValue) {
        if (cellValue == null) {
            return "";
        }
        cellValue = cellValue.replace("\r", "");
        cellValue = cellValue.replace("\n", "");
        cellValue = cellValue.replace("\b", "");
        return cellValue;
    }


}
